package dao.impl;

import bean.dto.CostPage;
import bean.vo.CostOption;
import bean.vo.PeopleCostVo;
import bean.vo.RoomListOption;
import commons.jdbc.basic.JDBCUtil;
import dao.CostManagementDao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * dao.impl
 * User: xlh
 * Date: 2024/12/18 9:38
 * motto:推到高塔
 * Description:
 * Version: V1.0
 */
public class CostManagementDaoImpl implements CostManagementDao{
    @Override
    public List<RoomListOption> findAllRoomList() {
        String  sql="select room_id,room_number from  room GROUP BY room_number";
        JDBCUtil jdbcUtil=new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql);
        if(query.size()>0){
            List<RoomListOption> list=new ArrayList<>();
            for (Map<String, Object> row : query) {//Community
                RoomListOption  roomListOption=new RoomListOption();
                roomListOption.setRoomId((Integer) row.get("room_id"));
                roomListOption.setRoomNumber((String) row.get("room_number"));
                list.add(roomListOption);
            }
            return  list;
        }
        return null;
    }
    @Override
    public List<CostOption> findByPage(CostPage costPage) {
        StringBuilder sql = new StringBuilder();

        // 构建基础的SELECT语句，选择需要的字段，根据实际表结构和字段名进行调整
        sql.append("SELECT u.user_id, " +
                "cr.charge_record_id,"+
                "u.user_name, " +
                "r.room_number, " +
                "et.type_name, " +
                "et.type_price, " +
                "et.type_time, " +
                "cr.payment_method, " +
                "cr.charge_date, " +
                "cr.is_dunned " +
                "FROM user u " +
                "JOIN room r ON u.room_id = r.room_id " +
                "JOIN charge_record cr ON cr.user_id = u.user_id " +
                "JOIN expense_type et ON cr.type_id = et.type_id " +
                "LEFT JOIN room rn ON cr.user_id = rn.user_id ");
        sql.append("WHERE 1=1 ");

        List<Object> parameters = new ArrayList<>();

        if (costPage.getRoomNumber()!= null &&!costPage.getRoomNumber().trim().isEmpty()) {
            parameters.add(costPage.getRoomNumber());
            sql.append("AND r.room_number LIKE CONCAT('%',?, '%')");
        }
        if (costPage.getTypeTime()!= null &&!costPage.getTypeTime().trim().isEmpty()) {
            parameters.add(costPage.getTypeTime());
            sql.append("AND et.type_time LIKE CONCAT('%',?, '%')");
        }
        if (costPage.getTypeName()!= null &&!costPage.getTypeName().trim().isEmpty()) {
            parameters.add(costPage.getTypeName());
            sql.append("AND et.type_name LIKE CONCAT('%',?, '%')");
        }

        sql.append("ORDER BY cr.charge_record_id DESC");
        sql.append(" limit "+(costPage.getPage()-1)*costPage.getLimit()+","+costPage.getLimit());

        System.out.println("sql: " + sql);
        System.out.println("parameters: " + parameters);

        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());

        List<CostOption> costOptions = new ArrayList<>();
        if (query.size() > 0) {
            for (Map<String, Object> row : query) {
                CostOption costOption = new CostOption();
                costOption.setChargeRecordId((Integer) row.get("charge_record_id"));
                costOption.setUserId((Integer) row.get("user_id"));
                costOption.setUserName((String) row.get("user_name"));
                costOption.setRoomNumber((String) row.get("room_number"));
                costOption.setTypeName((String) row.get("type_name"));
                costOption.setTypePrice((Double) row.get("type_price"));
                costOption.setTypeTime((String) row.get("type_time"));
                costOption.setPaymentMethod((String) row.get("payment_method"));
                costOption.setChargeDate((Date) row.get("charge_date"));

                Integer isDunnedValue = (Integer) row.get("is_dunned");
                if (isDunnedValue!= null) {
                    if (isDunnedValue == 0) {
                        costOption.setIsDunned("未缴费");
                    } else if (isDunnedValue == 1) {
                        costOption.setIsDunned("已缴费");
                    } else if (isDunnedValue == 2) {
                        costOption.setIsDunned("已催缴");
                    } else {
                        // 这里可以根据实际业务情况，决定对于其他意外值如何处理，比如设置一个默认值或者抛出异常等
                        costOption.setIsDunned("未知状态");
                    }
                } else {
                    // 如果从结果集中获取到的is_dunned值为null，也可以根据业务需求设置一个合适的值，比如空字符串等
                    costOption.setIsDunned("");
                }
                costOptions.add(costOption);
            }
            return costOptions;
        }
        return null;
    }

    @Override
    public Long findByTotal(CostPage costPage) {
        StringBuffer sql = new StringBuffer("SELECT COUNT(*) ct FROM charge_record cr ");
        sql.append("JOIN room r ON cr.room_id = r.room_id ");
        sql.append("JOIN expense_type et ON cr.type_id = et.type_id ");
        sql.append("WHERE 1=1 ");

        List<Object> parameters = new ArrayList<>();

        if (costPage.getRoomNumber() != null && !costPage.getRoomNumber().trim().isEmpty()) {
            parameters.add(costPage.getRoomNumber());
            sql.append("AND r.room_number LIKE CONCAT('%', ?, '%')");
        }
        if (costPage.getTypeTime() != null && !costPage.getTypeTime().trim().isEmpty()) {
            parameters.add(costPage.getTypeTime());
            sql.append("AND et.type_time LIKE CONCAT('%', ?, '%')");
        }
        if (costPage.getTypeName() != null && !costPage.getTypeName().trim().isEmpty()) {
            parameters.add(costPage.getTypeName());
            sql.append("AND et.type_name LIKE CONCAT('%', ?, '%')");
        }

        System.out.println("sql: " + sql);
        System.out.println("parameters: " + parameters);

        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());

        // 默认0条
        Long ct = 0L;
        if (query.size() > 0) {
            ct = ((Number) query.get(0).get("ct")).longValue();
        }
        return ct;
    }

    @Override
    public int updateCostById(PeopleCostVo peopleCostVo) {
        String sql = "UPDATE charge_record " +
                "SET payment_method =?,charge_date =?,is_dunned =1 " +
                "WHERE charge_record_id =?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        int update = jdbcUtil.update(sql,peopleCostVo.getPaymentMethod(),peopleCostVo.getCostTime(),peopleCostVo.getChargeRecordId());
        return update;
    }

    @Override
    public int Cuijiao(PeopleCostVo peopleCostVo) {
        String sql = "UPDATE charge_record " +
                "SET is_dunned =2 " +
                "WHERE charge_record_id =?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        int update = jdbcUtil.update(sql,peopleCostVo.getChargeRecordId());
        return update;}

    @Override
    public List<CostOption> loadAll(CostPage costPage) {
        StringBuilder sql = new StringBuilder();

        // 构建基础的SELECT语句，选择需要的字段，根据实际表结构和字段名进行调整
        sql.append("SELECT u.user_id, " +
                "cr.charge_record_id,"+
                "u.user_name, " +
                "r.room_number, " +
                "et.type_name, " +
                "et.type_price, " +
                "et.type_time, " +
                "cr.payment_method, " +
                "cr.charge_date, " +
                "cr.is_dunned " +
                "FROM user u " +
                "JOIN room r ON u.room_id = r.room_id " +
                "JOIN charge_record cr ON cr.user_id = u.user_id " +
                "JOIN expense_type et ON cr.type_id = et.type_id " +
                "LEFT JOIN room rn ON cr.user_id = rn.user_id ");
        sql.append("WHERE 1=1 ");
        List<Object> parameters = new ArrayList<>();
        sql.append("ORDER BY cr.charge_record_id DESC");
        System.out.println("sql: " + sql);
        System.out.println("parameters: " + parameters);

        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());

        List<CostOption> costOptions = new ArrayList<>();
        if (query.size() > 0) {
            for (Map<String, Object> row : query) {
                CostOption costOption = new CostOption();
                costOption.setChargeRecordId((Integer) row.get("charge_record_id"));
                costOption.setUserId((Integer) row.get("user_id"));
                costOption.setUserName((String) row.get("user_name"));
                costOption.setRoomNumber((String) row.get("room_number"));
                costOption.setTypeName((String) row.get("type_name"));
                costOption.setTypePrice((Double) row.get("type_price"));
                costOption.setTypeTime((String) row.get("type_time"));
                costOption.setPaymentMethod((String) row.get("payment_method"));
                costOption.setChargeDate((Date) row.get("charge_date"));

                Integer isDunnedValue = (Integer) row.get("is_dunned");
                if (isDunnedValue!= null) {
                    if (isDunnedValue == 0) {
                        costOption.setIsDunned("未缴费");
                    } else if (isDunnedValue == 1) {
                        costOption.setIsDunned("已缴费");
                    } else if (isDunnedValue == 2) {
                        costOption.setIsDunned("已催缴");
                    } else {
                        // 这里可以根据实际业务情况，决定对于其他意外值如何处理，比如设置一个默认值或者抛出异常等
                        costOption.setIsDunned("未知状态");
                    }
                } else {
                    // 如果从结果集中获取到的is_dunned值为null，也可以根据业务需求设置一个合适的值，比如空字符串等
                    costOption.setIsDunned("");
                }
                costOptions.add(costOption);
            }
            return costOptions;
        }
        return null;
    }

}
